using Homework8.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;

namespace Homework8.Services
{
    public class StudentService
    {
        public List<Student> GetAllStudents()
        {
            List<Student> students = new List<Student>();
            
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Student";
            using var command = new MySqlCommand(sql, connection);
            using var reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                students.Add(new Student
                {
                    StudentId = reader.GetInt32("StudentId"),
                    Name = reader.GetString("Name"),
                    Age = reader.IsDBNull(reader.GetOrdinal("Age")) ? null : reader.GetInt32("Age"),
                    Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString("Gender"),
                    ClassId = reader.GetInt32("ClassId"),
                    EnrollmentDate = reader.IsDBNull(reader.GetOrdinal("EnrollmentDate")) ? null : reader.GetString("EnrollmentDate"),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString("Address")
                });
            }
            
            return students;
        }
        
        public Student? GetStudentById(int studentId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Student WHERE StudentId = @StudentId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@StudentId", studentId);
            
            using var reader = command.ExecuteReader();
            
            if (reader.Read())
            {
                return new Student
                {
                    StudentId = reader.GetInt32("StudentId"),
                    Name = reader.GetString("Name"),
                    Age = reader.IsDBNull(reader.GetOrdinal("Age")) ? null : reader.GetInt32("Age"),
                    Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString("Gender"),
                    ClassId = reader.GetInt32("ClassId"),
                    EnrollmentDate = reader.IsDBNull(reader.GetOrdinal("EnrollmentDate")) ? null : reader.GetString("EnrollmentDate"),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString("Address")
                };
            }
            
            return null;
        }
        
        public List<Student> GetStudentsByClassId(int classId)
        {
            List<Student> students = new List<Student>();
            
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "SELECT * FROM Student WHERE ClassId = @ClassId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@ClassId", classId);
            
            using var reader = command.ExecuteReader();
            
            while (reader.Read())
            {
                students.Add(new Student
                {
                    StudentId = reader.GetInt32("StudentId"),
                    Name = reader.GetString("Name"),
                    Age = reader.IsDBNull(reader.GetOrdinal("Age")) ? null : reader.GetInt32("Age"),
                    Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString("Gender"),
                    ClassId = reader.GetInt32("ClassId"),
                    EnrollmentDate = reader.IsDBNull(reader.GetOrdinal("EnrollmentDate")) ? null : reader.GetString("EnrollmentDate"),
                    Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? null : reader.GetString("Address")
                });
            }
            
            return students;
        }
        
        public void AddStudent(Student student)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "INSERT INTO Student (Name, Age, Gender, ClassId, EnrollmentDate, Address) VALUES (@Name, @Age, @Gender, @ClassId, @EnrollmentDate, @Address); SELECT LAST_INSERT_ID();";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Gender", student.Gender ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);
            command.Parameters.AddWithValue("@EnrollmentDate", student.EnrollmentDate ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Address", student.Address ?? (object)DBNull.Value);
            
            var studentId = Convert.ToInt32(command.ExecuteScalar());
            student.StudentId = studentId;
            
            // 记录操作日志
            DBHelper.LogOperation("添加", "Student", studentId, $"添加学生：{student.Name}");
        }
        
        public void UpdateStudent(Student student)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            string sql = "UPDATE Student SET Name = @Name, Age = @Age, Gender = @Gender, ClassId = @ClassId, EnrollmentDate = @EnrollmentDate, Address = @Address WHERE StudentId = @StudentId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@StudentId", student.StudentId);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Gender", student.Gender ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);
            command.Parameters.AddWithValue("@EnrollmentDate", student.EnrollmentDate ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Address", student.Address ?? (object)DBNull.Value);
            
            command.ExecuteNonQuery();
            
            // 记录操作日志
            DBHelper.LogOperation("修改", "Student", student.StudentId, $"修改学生：{student.Name}");
        }
        
        public bool DeleteStudent(int studentId)
        {
            using var connection = DBHelper.GetConnection();
            connection.Open();
            
            // 获取学生名称用于日志
            string studentName = "";
            using (var nameCommand = new MySqlCommand("SELECT Name FROM Student WHERE StudentId = @StudentId", connection))
            {
                nameCommand.Parameters.AddWithValue("@StudentId", studentId);
                var nameResult = nameCommand.ExecuteScalar();
                studentName = nameResult != null ? nameResult.ToString() ?? "" : "";
            }
            
            // 执行删除
            string sql = "DELETE FROM Student WHERE StudentId = @StudentId";
            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@StudentId", studentId);
            
            int result = command.ExecuteNonQuery();
            
            if (result > 0)
            {
                // 记录操作日志
                DBHelper.LogOperation("删除", "Student", studentId, $"删除学生：{studentName}");
                return true;
            }
            
            return false;
        }
    }
} 